﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YinfangWeb.BLL.Model;
using YinfangWeb.Model;

namespace YinfangWeb.DAL
{
    public partial class ProductDataDAO
    {
        #region 编辑单条记录(不修改商品介绍字段)
        /// <summary>
        /// 编辑单条记录(不修改商品介绍字段)
        /// </summary>
        /// <param name="productData">实体</param>
        /// <param name="trans">事务</param>
        /// <returns>影响行数</returns>
        public int UpdateNotInfo(ProductData productData, IDbTransaction trans = null)
        {
            var sql = "UPDATE ProductData SET ProductState = @ProductState, MerchantDataID = @MerchantDataID, ProductType = @ProductType, ProductKindID = @ProductKindID, ProductName = @ProductName, LinkURL = @LinkURL, SellBase = @SellBase, TotalSell = @TotalSell, TotalStock = @TotalStock, ParamSum = @ParamSum, IsActivity = @IsActivity, IsRecommend = @IsRecommend, IsTop = @IsTop, IsMustbuy = @IsMustbuy, LimitMonth = @LimitMonth, RegDateTime = @RegDateTime, RegUserID = @RegUserID, RegFullName = @RegFullName, EditDateTime = @EditDateTime, EditUserID = @EditUserID, EditFullName = @EditFullName WHERE ProductDataID = @ProductDataID";
            var result = 0;
            if (trans != null)
            {
                var conn = trans.Connection;
                result = conn.Execute(sql, productData, trans);
                return result;
            }
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                result = conn.Execute(sql, productData);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 获取编辑初始化数据
        public ObjProductCreateResult GetEditInfo(int productDataID)
        {
            var sql = "select * from ProductData pd join ProductParam pp on pd.ProductDataID=pp.ProductDataID where pp.ParamDefault=1 and pd.ProductDataID=@ProductDataID";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                var model = conn.QueryFirstOrDefault<ObjProductCreateResult>(sql, new { ProductDataID = productDataID });
                return model;
            }
        }
        #endregion
        #region 分页列表
        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="orderBy">排序规则</param>
        /// <param name="whereClause">条件语句</param>
        /// <returns>分页列表</returns>
        public PagedList<ObjProduct> GetListOfProduct(int pageIndex, int pageSize, string whereClause = null, string orderBy = null)
        {
            pageIndex--;
            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            if (string.IsNullOrEmpty(orderBy))
            {
                orderBy = "pd.ProductDataID DESC";
            }
            var startItem = pageIndex * pageSize + 1;
            var endItem = (pageIndex + 1) * pageSize;
            var selFields = $@"pf.FileName,pd.ProductDataID,pd.ProductName,pd.ProductKindID,pd.TotalSell,pd.SellBase,pd.TotalStock,pd.ProductState,pd.RegFullName,pd.RegDateTime,
pp.PriceMarket,pk.KindName";
            var tabSql = $@"ProductData pd JOIN ProductParam pp on (pd.ProductDataID=pp.ProductDataID and pp.ParamDefault=1)
		LEFT JOIN ProductFile pf on pf.ProductFileID=pp.DefaultProductFileID
        LEFT JOIN ProductKind pk on pk.ProductKindID=pd.ProductKindID
    {whereClause}";
            var sql = $@"SELECT COUNT(pd.ProductDataID) FROM {tabSql}
SELECT *
FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS RowNum, {selFields} 
    FROM {tabSql}) AS result          
WHERE RowNum >= {startItem}
    AND RowNum <= {endItem}
ORDER BY RowNum";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                int count = 0;
                var list = new List<ObjProduct>();
                using (var q = conn.QueryMultiple(sql))
                {
                    count = q.Read<int>().Single();
                    list = q.Read<ObjProduct>().ToList();
                }
                conn.Close();

                var pagedList = new PagedList<ObjProduct>(list, pageIndex, pageSize, count);
                return pagedList;
            }
        }
        #endregion
        #region 卡片分页列表
        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="orderBy">排序规则</param>
        /// <param name="whereClause">条件语句</param>
        /// <returns>分页列表</returns>
        public PagedList<ObjProductCard> GetListOfProductCard(int pageIndex, int pageSize, string whereClause = null, string orderBy = null)
        {
            pageIndex--;
            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            if (string.IsNullOrEmpty(orderBy))
            {
                orderBy = "pd.ProductDataID DESC";
            }
            var startItem = pageIndex * pageSize + 1;
            var endItem = (pageIndex + 1) * pageSize;
            var selFields = $@"pf.FileName,pd.ProductDataID,pd.ProductName,pd.TotalSell,pd.SellBase,pp.PriceMarket,pp.PriceSell";
            var tabSql = $@"ProductData pd JOIN ProductParam pp on (pd.ProductDataID=pp.ProductDataID and pp.ParamDefault=1)
		LEFT JOIN ProductFile pf on pf.ProductFileID=pp.DefaultProductFileID
    {whereClause}";
            var sql = $@"SELECT COUNT(pd.ProductDataID) FROM {tabSql}
SELECT *
FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS RowNum, {selFields} 
    FROM {tabSql}) AS result          
WHERE RowNum >= {startItem}
    AND RowNum <= {endItem}
ORDER BY RowNum";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                int count = 0;
                var list = new List<ObjProductCard>();
                using (var q = conn.QueryMultiple(sql))
                {
                    count = q.Read<int>().Single();
                    list = q.Read<ObjProductCard>().ToList();
                }
                conn.Close();

                var pagedList = new PagedList<ObjProductCard>(list, pageIndex, pageSize, count);
                return pagedList;
            }
        }
        #endregion
    }
}
